package com.aaa.util;

import java.sql.*;
import java.util.*;

/**
 * @author ：Teacher陈（86521760@qq.com）
 * @date ：Created in 2021/7/14 10:31
 * @description：测试jdbc
 * @modified By：
 * @version: 1.0
 */
public class BaseDao {
    static final String driver = "com.mysql.jdbc.Driver";
    static final String url = "jdbc:mysql://localhost:3306/person?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8&useSSL=false";
    static final String user = "root";
    static final String password = "root";

    //加载驱动
    static {
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * @param
     * @return java.sql.Connection
     * @create by: Teacher陈（86521760@qq.com）
     * @description: 获取数据库连接
     * @create time: 2021/7/14 10:37
     */
    public Connection getConnection() {

        Connection connection = null;
        try {
            connection = DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;

    }

    /**
     * @param resultSet
     * @param preparedStatement
     * @param connection
     * @return void
     * @create by: Teacher陈（86521760@qq.com）
     * @description: 关闭连接
     * @create time: 2021/7/14 10:38
     */
    public void closeAll(ResultSet resultSet, PreparedStatement preparedStatement, Connection connection) {

        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (preparedStatement != null) {
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

    /**
     * @param sql
     * @param params
     * @return boolean
     * @create by: Teacher陈（86521760@qq.com）
     * @description:通用的增删改方法
     * @create time: 2021/7/14 10:39
     */
    public boolean executeUpdate(String sql, Object[] params) {
        Connection connection = getConnection();
        PreparedStatement preparedStatement = null;
        //创建一个 PreparedStatement对象，用于将参数化的SQL语句发送到数据库。
        try {
            preparedStatement = connection.prepareStatement(sql);
            //设置参数
            if (params != null && params.length > 0) {
                for (int i = 0; i < params.length; i++) {
                    preparedStatement.setObject(i + 1, params[i]);
                }
            }
            //执行sql
            int i = preparedStatement.executeUpdate();
            if (i > 0) {
                System.out.println("操作成功");
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll(null, preparedStatement, connection);
        }
        System.out.println("操作失败");
        return false;
    }
    /**
     * @param sql
     * @param params
     * @return Object
     * @create by: Teacher陈（86521760@qq.com）
     * @description:通用的增删改方法
     * @create time: 2021/7/14 10:39
     */
    public Object executeUpdatePrimaryKey(String sql, Object[] params) {
        Connection connection = getConnection();
        PreparedStatement preparedStatement = null;
        //创建一个 PreparedStatement对象，用于将参数化的SQL语句发送到数据库。
        try {
            preparedStatement = connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
            //设置参数
            if (params != null && params.length > 0) {
                for (int i = 0; i < params.length; i++) {
                    preparedStatement.setObject(i + 1, params[i]);
                }
            }
            //执行sql
            int i = preparedStatement.executeUpdate();
            if (i > 0) {
                System.out.println("操作成功");
                //返回主键
                ResultSet generatedKeys = preparedStatement.getGeneratedKeys();
                while (generatedKeys.next()){
                    Object object = generatedKeys.getObject(1);
                    return object;
                }

            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll(null, preparedStatement, connection);
        }
        System.out.println("操作失败");
        return null;
    }

    /**
     * @param sql
     * @param params
     * @return boolean
     * @create by: Teacher陈（86521760@qq.com）
     * @description:通用的查询方法
     * @create time: 2021/7/14 10:39
     */
    public List<Map<String, Object>> executeQuery(String sql, Object[] params) {
        Connection connection = getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<Map<String, Object>> rows = new ArrayList<>();
        //创建一个 PreparedStatement对象，用于将参数化的SQL语句发送到数据库。
        try {
            System.out.println("sql语句"+sql);
            preparedStatement = connection.prepareStatement(sql);
            //设置参数
            if (params != null && params.length > 0) {
                for (int i = 0; i < params.length; i++) {
                    preparedStatement.setObject(i + 1, params[i]);
                }
            }
            //执行sql
            //表示数据库结果集的数据表，通常通过执行查询数据库的语句生成。
            resultSet = preparedStatement.executeQuery();
            //next()Moves the cursor forward one row from its current position.
            //获取存储表结构的元数据
            ResultSetMetaData metaData = resultSet.getMetaData();
            while (resultSet.next()) {
                Map<String, Object> row = new HashMap<>();
                //metaData.getColumnCount()获取列的数量
                for (int i = 0; i < metaData.getColumnCount(); i++) {
                    //key是列名，value是列值
                    String key = metaData.getColumnLabel(i + 1);
                    Object value = resultSet.getObject(i + 1);
                    row.put(key, value);
                }
                rows.add(row);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll(resultSet, preparedStatement, connection);
        }
        return rows;
    }

    /**
     * @create by: Teacher陈（86521760@qq.com）
     * @description: 重构sql语句，目的是根据传入的参数，自动判断是否分页，是否排序，是否条件查询
     * @create time: 2021/7/19 9:29
     * @param sql,map
     * @return String
     */
    public String  makeSql(String sql,Map map){
        StringBuffer stringBuffer = new StringBuffer(sql+" where 1=1 ");
        //拼接where条件  where username like  '%2%'
        Set set = map.keySet();
        for (Object o : set) {
            String  key=o.toString();
            if(key.contains("search")){
                //如果map的key中包含和关键字search，那么就是条件查询的条件
                stringBuffer.append(" and "+key.replace("search","")+" like "+"'%"+map.get(key)+"%'");
            }
        }
        if(map.get("field")!=null){
            //排序
            stringBuffer.append(" order by "+map.get("field")+" "+map.get("order"));
        }
        if(map.get("page")!=null){
            //分页
            int page = Integer.parseInt(map.get("page") + "");
            int limit = Integer.parseInt(map.get("limit") + "");
            int start = (page - 1) * limit;
            // String sql = "select * from   userinfo limit  ?,? ";
            stringBuffer.append(" limit  "+start+","+ limit+" ");
        }

        return stringBuffer.toString();
    }

    public static void main(String[] args) {
       /* BaseDao baseDao = new BaseDao();
        String sql="insert into areainfo (areaname) values (?)";
        Object[] params={"惠及区"};
        Object primaryKey = baseDao.executeUpdatePrimaryKey(sql, params);
        System.out.println(primaryKey);*/
        /*String sql = "select * from areainfo";
        List<Map<String, Object>> mapList = myBaseDao.executeQuery(sql, null);
        for (Map<String, Object> map : mapList) {

            System.out.println(map);
        }*/

        for (int i = 0; i <50 ; i++) {
            UUID uuid = UUID.randomUUID();
            System.out.println(uuid);
        }

    }

}
